import base64
import json
import logging
import re
import time
from concurrent.futures import ThreadPoolExecutor, as_completed
from datetime import datetime

import requests
from Crypto.Hash import SHA256
from Crypto.PublicKey import RSA
from Crypto.Signature import pkcs1_15

from product_upload.domain.basic.basic_doba import BasicDoba
from product_upload.domain.basic.basic_product import BasicProduct
from product_upload.domain.basic.basic_supplier import BasicSupplier
from product_upload.util.basic.common_util import sleep_random_duration
from product_upload.util.basic.mysql_util import db_batch_insert, db_list_by_page, db_batch_update, db_get_count

logger = logging.getLogger(__name__)
app_key = '202502051336757526846242816'
public_key = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAhhEFoishJHyVlDRCmwDyX2Ii2XAUJMJ6Pmfxa4gAIUYvqTwJyjhNHIAWHLQud8vr4ZmizPCKBt4udwr6nRdN5i5+ZiE8vZ8nN/IBz+miXsxAZhI+5C2CCeCpeoTN5bk/zqAlZ1epprwr2CWuioFwTC1fLx2mNwsgtbPPFCtYUfzomYzsjrxN54bBW/KYiF+wQlNJ2uIYYEc2SMV2n2zL8O2epPzYvQxRBicl8EpEe1Y++JFbu34MYQfFeMADIj9zZgw62GQYRJd8OqqXyufNhg03wTAZG91/RlqeBtEiVpual+uP/oT6w2cvC8gkGuGksSBHv6XvjpeHCY9Do20kDQIDAQAB'
private_key = 'MIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggSlAgEAAoIBAQCGEQWiKyEkfJWUNEKbAPJfYiLZcBQkwno+Z/FriAAhRi+pPAnKOE0cgBYctC53y+vhmaLM8IoG3i53CvqdF03mLn5mITy9nyc38gHP6aJezEBmEj7kLYIJ4Kl6hM3luT/OoCVnV6mmvCvYJa6KgXBMLV8vHaY3CyC1s88UK1hR/OiZjOyOvE3nhsFb8piIX7BCU0na4hhgRzZIxXafbMvw7Z6k/Ni9DFEGJyXwSkR7Vj74kVu7fgxhB8V4wAMiP3NmDDrYZBhEl3w6qpfK582GDTfBMBkb3X9GWp4G0SJWm5qX64/+hPrDZy8LyCQa4aSxIEe/pe+Ol4cJj0OjbSQNAgMBAAECggEBAIEzKlCsa9Yz0hrvejjS4sEVXCxcHK2IVjKKDVjpyeZHM2tOlsixFW8f6S9kXVvJw9q4KvKB5SLVP8YtaHO3n7TynZFi+pxrImqB6UkrCPV+tZkogD95s9Y9ElcmYR7ANkO0hhdfnfk3YrF9LwX/0+810oMfea2WJcG2NRxk4UqwkaGrwrWQ17oxigugf/f1yhCVobmJ9qd+jan4FqPBIFg95mxOGalewsTuZbRh+YtZYTvjoLDUDFQahe9ilALoKd6lZjI8TuqErTQVPGUf0Gbl2B5jHIz7Ilp33OvHOKx5BsO1EL4ADvssqSn0IqAYIDHbncquyRJJ3Vg8bfjHYakCgYEA+XRJk5IwN/Lx9vv866KFLpjNGLmnQAm83biy92mrUKXMHMKOQGdDaYkvJsgsHYYtU0u35hMAGOpNA3MF70jtO7yspsO/ZwSfdX2w7+Ro2YP8P9Ja0R4EsAq277EldsIxci2+1bh+F65otjCaF8UXpNeqshl9D+z4a7vJ7ijuQ8cCgYEAiZWdmwSXQGGkmwshIwSeiIsRl7kNcnCKql99djRByxb++npt/LjQXrKfN9n6oyFH1kbOuVrgOz1OgGIXjLigVo2DhRncqgYEGzqNuNFHqu6LYggpU8MXVry+vEFWqXs6gH+odSj2GBxZJMV/Vt/2w1+24IEy96OOIf/d54HA8YsCgYEAxvmV0qsR4R7dGCfplz7cQs3JEsbLfI4H40d3IVhdOQf2pp4df4uevuiY/rM+VRJ4xu8nsdJICpehdHXkLviZmkOl56NN4etTEhF+9+rWI2Je671riVALZyw0vT4s5uyBKlUI2kq98T/B8EkWMI2ajXclhchE9FYN1UlGCb0GSHMCgYB/aCZv1BuAiitVTEQeWnu4dBgRiVMzg5ZVWWIkYRYATMZIC7HokviaiQy6OshH4lzUx/NNYCM/mHEmoC+wp5QLMrb1PdLDPBj3Ygy1dk/IsWgvmuEyaPom31g9mr/26F9veUf1Xr4t66p9S8q1F2c621rNobch7bqEL549cUHMKwKBgQCBgFMvO/drGYJB/W/rnX23KfSd6FJ06b9q+M3qG26oQhoAToqJOvr8ReRBSHwfigF6KTifT74fq52qR2csgbXadWI7OgRpgRPkvbepe7OaM6N24a3Ob4YZ4zkFvd8M54KEB5HuLgK5A1wu68aGurHFepEZGWwnnpNLhiCozpPBjw=='

region_code_map = {"US": "US"}


def sign(timestamp):
    content_for_sign = f"appKey={app_key}&signType=rsa2&timestamp={timestamp}"
    private_key_decode = base64.b64decode(private_key)
    key = RSA.import_key(private_key_decode)
    hash_obj = SHA256.new(content_for_sign.encode())
    signature = pkcs1_15.new(key).sign(hash_obj)
    return base64.b64encode(signature).decode()


def generate_request_headers():
    timestamp = str(int(time.time() * 1000))
    sign_value = sign(timestamp)
    return {'appKey': app_key, 'signType': 'rsa2', 'timestamp': timestamp, 'sign': sign_value}


def filter_doba_html_data(html_content):
    if html_content is None or html_content == "":
        return ""
    cleaned_text = re.sub(r'<[^>]+>', '\n', html_content)
    non_ascii_pattern = re.compile(r'[^\x00-\x7F]+')
    cleaned_text_without_unicode = non_ascii_pattern.sub('', cleaned_text)
    entity_pattern = re.compile(r'&#[0-9]+;')
    final_cleaned_text = entity_pattern.sub('', cleaned_text_without_unicode)
    return final_cleaned_text.strip().replace("\n\n\n", "\n").replace("\n\n", "\n")


# 查询库存和价格,item_nos用逗号分割,返回值是dict,key=itemNo,val=price + quantity
# def get_product_price_and_inventory(item_nos):
#     url = "https://openapi.doba.com/api/goods/doba/stock"
#     params = {'itemNo': item_nos}
#     try:
#         response = requests.get(url, params=params, headers=generate_request_headers(), timeout=20)
#         if response.status_code == 200:
#             data = response.json()
#             if data.get("responseCode") == "000000":
#                 business_data = data.get("businessData", {})
#                 if business_data.get("businessStatus") == "000000" and business_data.get("businessMessage") == "Success":
#                     products = business_data.get("data", [])
#                     quantity_price_map = {}
#                     for product in products:
#                         quantity_price_map[product.get("itemNo")] = {"price": product.get("sellingPrice"), "quantity": product.get("availableNum")}
#                     return quantity_price_map
#                 else:
#                     print(business_data)
#             else:
#                 print(data)
#         return None
#     except Exception as e:
#         print("error:get_product_price_and_inventory:", e)
#         return None


def get_product_price_and_inventory(item_nos):
    url = "https://openapi.doba.com/api/goods/doba/stock"
    params = {'itemNo': item_nos}
    max_retries = 1  # 允许一次重试，总共两次请求
    for attempt in range(max_retries + 1):
        try:
            response = requests.get(url, params=params, headers=generate_request_headers(), timeout=20)
            if response.status_code != 200:
                logger.error(f"error:get_product_price_and_inventory:HTTP Error {response.status_code}, attempt {attempt + 1}")
                if attempt < max_retries:
                    continue  # 进行重试
                logger.error(f"error:get_product_price_and_inventory:Final attempt failed. Response:{response.text}")
                return None
            data = response.json()
            if data.get("responseCode") != "000000":
                logger.error(f"error:get_product_price_and_inventory:API Error:{data}")
                return None  # 业务响应码错误不重试
            business_data = data.get("businessData", {})
            if business_data.get("businessStatus") != "000000" or business_data.get("businessMessage") != "Success":
                logger.error("error:get_product_price_and_inventory:Business Error:", business_data)
                return None  # 业务状态错误不重试
            products = business_data.get("data", [])
            return {product["itemNo"]: {"price": product.get("sellingPrice"), "quantity": product.get("availableNum")} for product in products}
        except (requests.exceptions.Timeout, requests.exceptions.ConnectionError) as e:
            logger.error(f"error:get_product_price_and_inventory:Network Error ({type(e).__name__}), attempt {attempt + 1}")
            if attempt >= max_retries:
                logger.error("error:get_product_price_and_inventory:Max retries exceeded.")
                return None
            # 继续重试
        except Exception as e:
            logger.error(f"error:get_product_price_and_inventory:Unexpected error:{str(e)}")
            return None
    return None


# 分页查询供应商下的spu列表
def get_spu_list(supplier_id=None, page_number=1, page_size=50, ship_from=None, ship_to=None, listing_time_after=None,
                 listing_time_before=None, min_inventory=None):
    url = "https://openapi.doba.com/api/goods/doba/spu/list"
    params = {
        'busiId': supplier_id,
        'pageNumber': page_number,
        'pageSize': page_size,
        'shipFrom': ship_from,
        'shipTo': ship_to,
        'listingTimeAfter': listing_time_after,
        'listingTimeBefore': listing_time_before,
        'minInventory': min_inventory
    }
    params = {key: value for key, value in params.items() if value is not None}
    try:
        response = requests.get(url, headers=generate_request_headers(), params=params)
        if response.status_code == 200:
            data = response.json()
            if data.get("responseCode") == "000000":
                business_data = data.get("businessData", {})
                if business_data.get("businessStatus") == "000000" and business_data.get(
                        "businessMessage") == "Success":
                    products = business_data.get("data", {}).get("goodsList", [])
                    tmp_spu_no_list = []
                    for product in products:
                        spu_no = product.get("spuNo")
                        tmp_spu_no_list.append(spu_no)
                    return tmp_spu_no_list
        return None
    except Exception as e:
        logger.error(f"error:get_spu_list:{e}")
        return None


# 通过spu_no查询spu_nos下的具体详情,dict返回itemNo-sku的详情
def get_spu_detail(item_no=None, spu_id=None, spu_nos=None, select_type="default"):
    url = "https://openapi.doba.com/api/goods/doba/spu/detail"
    params = {'itemNo': item_no, 'spuId': spu_id, 'spuNo': spu_nos}
    params = {key: value for key, value in params.items() if value is not None}
    response = requests.get(url, headers=generate_request_headers(), params=params, timeout=20)
    try:
        if response.status_code == 200:
            data = response.json()
            if data.get("responseCode") == "000000":
                business_data = data.get("businessData", {})
                if business_data.get("businessStatus") == "000000" and business_data.get(
                        "businessMessage") == "Success":
                    spu_details = business_data.get("data", [])
                    return collect_sku_by_spu(spu_details) if select_type == "default" else collect_sku_by_spu_sample(spu_details)
        return None
    except Exception as e:
        logger.error(f"error:get_spu_detail:{e}")
        return None


# 分页获取供应商列表
def get_supplier_list(page_number=1, page_size=100, supplier_id=None):
    url = "https://openapi.doba.com/api/supplier/doba/list"
    params = {
        'pageNumber': page_number,
        'pageSize': page_size,
        'supplierId': supplier_id
    }
    params = {key: value for key, value in params.items() if value is not None}
    try:
        response = requests.get(url, headers=generate_request_headers(), params=params, timeout=20)
        if response.status_code == 200:
            data = response.json()
            if data.get("responseCode") == "000000":
                business_data = data.get("businessData", {})
                if business_data.get("businessStatus") == "000000" and business_data.get(
                        "businessMessage") == "Success":
                    suppliers = business_data.get("data", [])
                    tmp_suppliers = []
                    for supplier in suppliers:
                        tmp_suppliers.append(
                            {"code": supplier.get('supplierId', ""), "name": supplier.get('supplierName', "")})
                    return tmp_suppliers
        return None
    except Exception as e:
        logger.error(f"error:get_supplier_list:{e}")
        return None


# 获取运费,参数:item_nos,返回dict的运费
def get_item_shipping_fee(item_nos, ship_to_country="US", ship_to_province=None, ship_to_city=None,
                          ship_to_zip_code=None, platform_id=None, ship_id=None):
    url = "https://openapi.doba.com/api/shipping/doba/cost/goods"
    items = item_nos.split(",")
    goods = []
    for item in items:
        goods.append({"itemNo": item, "quantity": 1})
    data = {"goods": goods, "shipToCountry": ship_to_country, "shipToProvince": ship_to_province,
            "shipToCity": ship_to_city, "shipToZipCode": ship_to_zip_code, "platformId": platform_id, "shipId": ship_id}
    data = {key: value for key, value in data.items() if value is not None}
    try:
        response = requests.post(url, headers=generate_request_headers(), json=data)
        if response.status_code == 200:
            data = response.json()
            if data.get("responseCode") == "000000":
                business_data = data.get("businessData", [])
                if business_data:
                    ship_fee_map = {}
                    for item in business_data:
                        if item.get("data"):
                            item_cost_data = item.get("data", None)
                            if item.get("successful"):
                                lowest_ship_fee = min([cost['shipFee'] for cost in item_cost_data["costs"]])
                                ship_fee_map[item_cost_data["itemNo"]] = lowest_ship_fee
                            else:
                                ship_fee_map[item_cost_data["itemNo"]] = 0
                    return ship_fee_map
        return None
    except Exception as e:
        logger.error(f"error:get_item_shipping_fee:{e}")
        return None


"""
以上全是DOBA API方法, 下面是业务代码
"""


def collect_sku_by_spu_sample(spu_obj_list):
    sku_list = []
    for spu_obj in spu_obj_list:
        spu_no = spu_obj.get("spuNo")
        processing_time = str(spu_obj.get("processingTime")) if spu_obj.get("processingTime") else "0"
        product_parameter = spu_obj.get("productParameter")
        if product_parameter:
            if product_parameter.get("marketRestrictions"):
                not_available = product_parameter.get("marketRestrictions").replace(";",",").replace(" ", "").lower()
                processing_time = '{"processing_time":' + processing_time + '}'
                sku_list.append([spu_no, not_available, processing_time])




def collect_sku_by_spu(spu_obj_list):
    sku_list = []
    for spu_obj in spu_obj_list:
        title = spu_obj.get("title")
        spu_no = spu_obj.get("spuNo")
        supplier_code = spu_obj.get("busiId")
        supplier_name = spu_obj.get("sellerName")
        high_lights = spu_obj.get("sellPoint", [])
        brand = spu_obj.get("brand", "")
        processing_time = str(spu_obj.get("processingTime")) if spu_obj.get("processingTime") else "0"
        class_name = spu_obj.get("cateName", "")
        not_available = ""
        description = filter_doba_html_data(spu_obj.get("goodsDesc"))
        product_parameter = spu_obj.get("productParameter")
        dimensions = [1, 1, 1, 1]
        dimensions_str = ""
        packages = [1, 1, 1, 1]
        packages_str = ""
        if product_parameter:
            ds_goods_size = product_parameter.get("dsGoodsSizeVO")
            if product_parameter.get("marketRestrictions"):
                not_available = product_parameter.get("marketRestrictions")
            if ds_goods_size:
                item_length = ds_goods_size.get("length", 1)
                item_length = item_length if item_length and float(item_length) > 0 else 1
                item_width = ds_goods_size.get("width", 1)
                item_width = item_width if item_width and float(item_width) > 0 else 1
                item_height = ds_goods_size.get("height", 1)
                item_height = item_height if item_height and float(item_height) > 0 else 1
                item_weight = ds_goods_size.get("weight", 1)
                item_weight = item_weight if item_weight and float(item_weight) > 0 else 1
                dimensions = [item_length, item_width, item_height, item_weight]
                dimensions_str = f'length:{item_length},width:{item_width},height:{item_height},weight:{item_weight},dimUnit:in,weightUnit:lb'
        packaging_information = spu_obj.get("packagingInformation")
        if packaging_information:
            packaging_size = packaging_information.get("packagingSize")
            pkg_length, pkg_width, pkg_height, pkg_weight = 1, 1, 1, 1
            if packaging_size:
                pkg_length = packaging_size.get("length", 1)
                pkg_length = pkg_length if pkg_length and float(pkg_length) > 0 else 1
                pkg_width = packaging_size.get("width", 1)
                pkg_width = pkg_width if pkg_width and float(pkg_width) > 0 else 1
                pkg_height = packaging_size.get("height", 1)
                pkg_height = pkg_height if pkg_height and float(pkg_height) > 0 else 1
            packaging_weight = packaging_information.get("packagingWeight")
            if packaging_weight:
                pkg_weight = packaging_weight.get("weight", 1)
                pkg_weight = pkg_weight if pkg_weight and float(pkg_weight) > 0 else 1
            packages = [pkg_length, pkg_width, pkg_height, pkg_weight]
            packages_str = f'length:{pkg_length},width:{pkg_width},height:{pkg_height},weight:{pkg_weight},dimUnit:in,weightUnit:lb'
        sku_obj_list = spu_obj.get("children", [])
        for sku_obj in sku_obj_list:
            sku_link = sku_obj.get("skuUrl")
            images_list = sku_obj.get("skuPicList", [])
            main_image = images_list[0] if len(images_list) > 0 else ""
            variant_props = sku_obj.get("variantProps")
            var_fields = [f'{var.get("propName", "")}:{var.get("propValue", "")}' for var in variant_props]
            stocks = sku_obj.get("stocks", [])
            if stocks:
                for stock in stocks:
                    region = stock.get("regionId")
                    item_no = stock.get("itemNo")
                    quantity = stock.get("availableNum")
                    price = stock.get("sellingPrice")
                    product_info = f'title:\n{title}\n'
                    if high_lights:
                        product_info += f'highlights:\n{",".join(high_lights)}\n'
                    if dimensions_str:
                        product_info += f'dimensions:{dimensions_str}\n'
                    if packages_str:
                        product_info += f'packages:{packages_str}\n'
                    if var_fields:
                        product_info += f'Specification:\n{",".join(var_fields)}\n'
                    product_info += f'description:\n{description}'
                    text_json = json.dumps({"sku": item_no, "images_list": images_list, "dimensions": dimensions, "packages": packages})
                    remark = '{"processing_time":' + processing_time + '}'
                    item_field = {"remark": remark, "class_name": class_name, "not_available": not_available,
                                  "sku": item_no, "packages": " * ".join([str(x) for x in packages]),
                                  "dimensions": " * ".join([str(x) for x in dimensions]), "link": sku_link,
                                  "main_image": main_image, "quantity": quantity, "price": price,
                                  "title": title, "region": region,
                                  "spu": spu_no, "supplier_code": supplier_code, "supplier_name": supplier_name,
                                  "brand": brand, "product_info": product_info, "json_text": text_json}
                    sku_list.append(item_field)
    return sku_list


# 后续追加doba供应商
def doba_append_supplier():
    suppliers_all = []
    for page in range(1, 99999):
        suppliers = get_supplier_list(page)
        if not suppliers:
            break
        suppliers_all.extend(suppliers)
    db_supp = db_list_by_page("basic_supplier", "code", "platform = 'DB'", BasicSupplier, 1, 9999999)
    db_supp_code_list = [x.code for x in db_supp]
    suppliers_dict = {x["code"].strip(): x["name"].strip() for x in suppliers_all if
                      x["code"].strip() not in db_supp_code_list}
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    insert_suppliers = []
    for code, name in suppliers_dict.items():
        insert_suppliers.append([0, 'DB', code, name, create_time])
    db_batch_insert("basic_supplier", ["status", "platform", "code", "name", "create_time"], insert_suppliers)


# 追加新产品到_basic_boba
def doba_api_append_goods_to_doba(conditions):
    if not conditions:
        conditions = "status = 1 and platform = 'DB'"
    supplier_list = db_list_by_page("basic_supplier", "code,name", conditions, BasicSupplier, 1, 99999)
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for supplier in supplier_list:
        for page_spu in range(1, 999999):
            # 下次使用的时候 需要带上 日期条件。以追加供应商的新品
            spu_list = get_spu_list(supplier_id=supplier.code, page_size=50, page_number=page_spu, ship_from="US", ship_to="US")
            if not spu_list:
                break
            spus = ",".join(spu_list)
            sku_items = get_spu_detail(spu_nos=spus)
            if sku_items:
                fields = ["remark", "not_available", "class_name", 'title', 'spu', 'sku', 'region', 'supplier_code',
                          'supplier_name', 'brand', 'link', 'quantity', 'price', 'main_image', 'dimensions', 'packages',
                          'json_text', 'product_info', 'create_time']
                values = []
                for item in sku_items:
                    if db_get_count("basic_doba", f'sku="{item.get("sku")}"'):
                        continue
                    values.append(
                        [item.get("remark"), item.get("not_available"), item.get("class_name"), item.get("title"),
                         item.get("spu"), item.get("sku"), item.get("region"), item.get("supplier_code"),
                         item.get("supplier_name"), item.get("brand"), item.get("link"),
                         item.get("quantity"), item.get("price"),
                         item.get("main_image"), item.get("dimensions"), item.get("packages"), item.get("json_text"),
                         item.get("product_info"), create_time])
                db_batch_insert("basic_doba", fields, values)
                logger.info(f"------插入数据:{supplier.name},page_spu:{page_spu},sku count:{len(sku_items)}")


# 刷新一下doba的 hand-time和 not_available
def db_flush_hand_time_and_not_available():
    for page in range(1, 9999999):
        spu_list = db_list_by_page("basic_product", "DISTINCT spu", "platform='DB' and status =1 and published = 1", None, page, 10000, "spu", "desc")
        if not spu_list:
            break
        for idx in range(0, len(spu_list), 50):
            tmp_list = spu_list[idx:idx + 50]
            spus = ",".join([x[0] for x in tmp_list])
            sku_items = get_spu_detail(spu_nos=spus, select_type="sample")
            if sku_items:
                db_batch_update("basic_product", ["spu", "not_available","extra_field"], sku_items)


def handle_json_text(sku_item):
    json_text = json.loads(sku_item.json_text)
    dimensions = json_text.get("dimensions", [])
    new_dimensions = []
    for dimension in dimensions:
        if dimension is None or dimension <= 0:
            new_dimensions.append(1)
        else:
            new_dimensions.append(dimension)
    packages = json_text.get("packages", [])
    new_packages = []
    for package in packages:
        if package <= 0:
            new_packages.append(1)
        else:
            new_packages.append(package)
    dimensions_str = " * ".join([str(dimension) for dimension in new_dimensions])
    packages_str = " * ".join([str(package) for package in new_packages])
    json_text["packages"] = new_packages
    json_text["dimensions"] = new_dimensions
    sku_item.dimensions = dimensions_str
    sku_item.packages = packages_str
    sku_item.json_text = json.dumps(json_text)


# doba的新品拷贝到basic表
def doba_copy_to_basic(extra_query):
    supplier_list = db_list_by_page("basic_supplier", "code", "status = 1 and platform = 'DB' ", BasicSupplier, 1, 99999)
    status_code_str = ",".join([f'"{x.code}"' for x in supplier_list])
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for page in range(1, 9999):
        need_insert_data = []
        conditions = f'not_available not like "%amazon%" and region like "%US%" and supplier_code in ({status_code_str}) and sku not in (select sku from basic_product where platform = "DB")'
        if extra_query:
            conditions += extra_query
        print(conditions)
        doba_res = db_list_by_page("basic_doba", ",".join(BasicDoba.fields), conditions, BasicProduct, page, 10000)
        if not doba_res:
            break
        for sku_item in doba_res:
            if not sku_item.json_text:
                continue
            # 处理尺寸不大于0的情况,进行对尺寸和包装的合规性校验
            handle_json_text(sku_item)
            if sku_item.not_available:
                sku_item.not_available = ",".join(sku_item.not_available.split("; "))
            need_insert_data.append(
                [sku_item.sku, sku_item.spu, sku_item.region, sku_item.supplier_code, sku_item.supplier_name,
                 sku_item.link, sku_item.main_image,
                 sku_item.dimensions, sku_item.packages, sku_item.json_text, sku_item.product_info,
                 sku_item.not_available, sku_item.title, sku_item.remark, "DB", 0, 1, create_time])
        print(page, "insert_db_list", len(need_insert_data))
        if need_insert_data:
            db_batch_insert("basic_product", ["sku", "spu", 'region', 'supplier_code', "supplier_name", 'link', "main_image", "dimensions", "packages", "json_text",
                                              "product_info", "not_available", "title", "extra_field", "platform", "status", "published", "create_time"], need_insert_data)


# 更新价格和库存
def doba_update_price_and_quantity():
    # 获取所有sku列表
    sku_list_page = []
    for page in range(1, 99999999):
        sku_list_this = db_list_by_page("basic_product", "id,published,sku,price,quantity", "platform = 'DB'", BasicProduct, page, 50000)
        if not sku_list_this:
            break
        sku_list_page.extend(sku_list_this)
    logger.info(f'start: doba update price and quantity...')
    with ThreadPoolExecutor(max_workers=22) as executor:
        futures = []
        for i in range(0, len(sku_list_page), 50):
            sku_list = sku_list_page[i:i + 50]
            futures.append(executor.submit(thread_doba_price_quantity, sku_list))
            sleep_random_duration(0.05, 0.08)
            if len(futures) >= 22:
                for future in as_completed(futures):
                    try:
                        future.result()
                    except Exception as e:
                        logger.error(f"Task execution failed: {e}")
                sleep_random_duration()
                futures.clear()
        logger.info(f"Doba price and quantity fetch completed.")


# 线程处理的逻辑
def thread_doba_price_quantity(sku_list):
    sku_id_map = {x.sku: x.id for x in sku_list}
    sku_quantity_map = {x.sku: x.quantity for x in sku_list}
    sku_published_map = {x.sku: x.published for x in sku_list}
    sku_price_map = {x.sku: float(x.price) for x in sku_list if x.price}
    for_skus = [x.sku for x in sku_list]
    sku_str = ",".join(for_skus)
    quantity_price_dict = get_product_price_and_inventory(sku_str)
    need_update_list = []
    need_update_image_list = []
    if quantity_price_dict:
        for sku in for_skus:
            quantity_price = quantity_price_dict.get(sku) if quantity_price_dict.get(sku) else {}
            api_price = round(float(quantity_price.get("price", 0)), 2) if quantity_price.get("price") else 0
            published = 0
            if api_price:
                published = 1
            api_quantity = int(quantity_price.get("quantity")) if quantity_price.get("quantity") else 0
            if api_quantity != sku_quantity_map.get(sku, 0) or api_price != sku_price_map.get(sku, 0) or published != sku_published_map.get(sku, 1):
                need_update_list.append([sku_id_map[sku], published, api_quantity, api_price])
            if published != sku_published_map.get(sku, 1):
                need_update_image_list.append([sku_id_map[sku], published])
        if need_update_list:
            db_batch_update("basic_product", ["id", "published", "quantity", "price"], need_update_list)
        if need_update_image_list:
            db_batch_update("basic_image", ["basic_id", "published"], need_update_image_list)


# 更新运费
def doba_update_ship_fee():
    sku_list_page = []
    for page in range(1, 99999999):
        sku_list_this = db_list_by_page("basic_product", "id,sku,ship_fee", "platform = 'DB'", BasicProduct, page, 100000, "id", "desc")
        if not sku_list_this:
            break
        sku_list_page.extend(sku_list_this)
    logger.info(f'start: doba update ship_fee...')
    with ThreadPoolExecutor(max_workers=15) as executor:
        futures = []
        for i in range(0, len(sku_list_page), 50):
            sku_list = sku_list_page[i:i + 50]
            futures.append(executor.submit(thread_doba_ship_fee, sku_list))
            sleep_random_duration(0.08, 0.1)
            if len(futures) >= 15:
                for future in as_completed(futures):
                    try:
                        future.result()
                    except Exception as e:
                        logger.error(f"Task execution failed: {e}")
                sleep_random_duration()
                futures.clear()
        logger.info(f"Doba ship_fee fetch completed.")


# 线程处理的逻辑
def thread_doba_ship_fee(sku_list):
    sku_id_map = {x.sku: x.id for x in sku_list}
    sku_ship_fee_map = {x.sku: float(x.ship_fee) for x in sku_list if x.ship_fee}
    for_skus = [x.sku for x in sku_list]
    sku_str = ",".join(for_skus)
    ship_fee_dict = get_item_shipping_fee(sku_str)
    need_update_list = []
    if ship_fee_dict:
        for sku in for_skus:
            api_ship_fee = ship_fee_dict.get("price", 0)
            db_ship_fee = sku_ship_fee_map.get(sku, 0)
            if api_ship_fee != db_ship_fee:
                need_update_list.append([sku_id_map[sku], api_ship_fee])
        if need_update_list:
            db_batch_update("basic_product", ["id", "ship_fee"], need_update_list)


if __name__ == "__main__":
    db_flush_hand_time_and_not_available()
    pass
